变量|批量_超多绑定变量导致异常的一个案例

篇首语：本文由编程笔记#小编为大家整理，主要介绍了超多绑定变量导致异常的一个案例相关的知识，希望对你有一定的参考价值。

最近生产上出现一个问题&＃xff0c;某个应用单个SQL中绑定变量个数超过了65535个&＃xff0c;导致数据库出现了异常终止的现象。

通过trace&＃xff0c;看到很多这样的信息(为了脱敏&＃xff0c;此处引用MOS的例子)&＃xff0c;

导致问题的SQL诸如这种&＃xff0c;

BEGIN
UPDATE TEST SET
C1 &＃61; :1,
C2 &＃61; :2,
C3 &＃61; :3,
C4 &＃61; :4,
C5 &＃61; :5,
C6 &＃61; :6,
C7 &＃61; :7
WHERE ID &＃61; :8 AND ACC &＃61; :9
;
UPDATE TEST SET
C1 &＃61; :10,
C2 &＃61; :11,
C3 &＃61; :12,
C4 &＃61; :13,
C5 &＃61; :14,
C6 &＃61; :15,
C7 &＃61; :16
WHERE ID &＃61; :17 AND ACC &＃61; :18
;
UPDATE TEST SET
C1 &＃61; :19,
C2 &＃61; :20,
C3 &＃61; :21,
C4 &＃61; :22,
C5 &＃61; :23,
C6 &＃61; :24,
C7 &＃61; :25
WHERE ID &＃61; :26 AND ACC &＃61; :27
;
...
UPDATE TEST SET
C1 &＃61; :619001,
C2 &＃61; :619002,
C3 &＃61; :619003,
C4 &＃61; :619004,
C5 &＃61; :619005,
C6 &＃61; :619006,
C7 &＃61; :619007,
WHERE ID &＃61; :619008 AND ACC &＃61; :619009
; END;

这是一段PL/SQL&＃xff0c;其中批量拼接了很多的update&＃xff0c;1条SQL是9个变量&＃xff0c;总共六十多万个&＃xff0c;粗略算下来&＃xff0c;应该是6万多条SQL&＃xff0c;显然他的逻辑是一次性更新6万多条记录。

此时问题有两个&＃xff0c;

(1) 执行一次SQL为什么带着这么多绑定变量&＃xff1f;

(2) 为什么执行这么多绑定变量的SQL会导致数据库出现问题&＃xff1f;

MOS的这篇文章《Instance crashed after ORA-7445 [opiaba] and ORA-600 [17147] (Doc ID 1466343.1)》给出了第二个问题的答案&＃xff0c;

如果在同一条SQL或PL/SQL语句中使用超过65535个绑定变量&＃xff0c;则数据库实例会因为ORA-7445的错误导致出现ORA-600&＃xff0c;进而异常中断。

官方给出的方案&＃xff0c;是打个12578873的patch&＃xff0c;但是他只会解决因为绑定变量超多导致的实例终止的问题&＃xff0c;超过65535绑定变量的语句还是不能执行&＃xff0c;因此无论是workaround&＃xff0c;还是终极解决&＃xff0c;都是建议不要使用绑定变量超过65535个&＃xff0c;这就回到了第一个问题&＃xff0c;超过65535个绑定变量的场景&＃xff0c;合理么&＃xff1f;

其实针对这案例&＃xff0c;60多万个绑定变量&＃xff0c;不是应用“有意而为之”的&＃xff0c;他想做的是一次性批量更新6万多记录&＃xff0c;但是他在MyBatis的mapper中用的是for-each&＃xff0c;这就会循环遍历list&＃xff0c;"BEGIN"开头&＃xff0c;";END"结尾&＃xff0c;";"作为separator&＃xff0c;拼接出的PL/SQL&＃xff0c;一次性执行&＃xff0c;



UPDATE TEST SET
C1 &＃61; #item.c1,
C2 &＃61; #item.c2,
C3 &＃61; #item.c3,
C4 &＃61; #item.c4,
C5 &＃61; #item.c5,
C6 &＃61; #item.c6,
C7 &＃61; #item.c7
WHERE ID &＃61; #item.id AND ACC &＃61; #item.acc



P. S. 关于MyBatis的for-each&＃xff0c;可以参考官方文档的介绍&＃xff0c;

https://mybatis.org/mybatis-3/zh/dynamic-sql.html

如果要批量更新这些数据&＃xff0c;该怎么做&＃xff1f;

官网介绍了&＃xff0c;应该用的是openSession时指定参数ExecutorType.BATCH&＃xff0c;他将批量执行所有的更新语句&＃xff0c;

https://mybatis.org/mybatis-3/zh/java-api.html#sqlSessions

mapper就是单条update语句&＃xff0c;

openSession指定了ExecutorType.BATCH&＃xff0c;并且设置了1000条更新执行一次提交的逻辑&＃xff0c;

P. S. MyBatis工程的构建&＃xff0c;可以参考《MyBatis Generator的使用和坑》。

从数据库端看&＃xff0c;执行如下SQL&＃xff0c;不再出现上万个绑定变量的情况&＃xff0c;

UPDATE TEST SET
C1 &＃61; :1,
C2 &＃61; :2,
C3 &＃61; :3,
C4 &＃61; :4,
C5 &＃61; :5,
C6 &＃61; :6,
C7 &＃61; :7
WHERE ID &＃61; :8 AND ACC &＃61; :9;

以上才是真正的批量更新操作&＃xff0c;从上面的操作&＃xff0c;还可知道原始的BEGIN ... END的PL/SQL需要执行所有的update语句才提交一次&＃xff0c;不是批量提交&＃xff0c;算是一个隐患&＃xff0c;这种拼接SQL&＃xff0c;只适合小数据量的操作。

有同学讨论说怎么避免这种问题&＃xff1f;这就可能有很多路径了&＃xff0c;一个是开发规范中可以将这种坑实时更新进来&＃xff0c;让设计开发人员有所了解&＃xff0c;数据审核平台&＃xff0c;则可以从技术层面验证这种“海量”绑定变量的场景&＃xff0c;归根结底&＃xff0c;这个问题考验的还是设计开发人员对MyBatis以及绑定变量的理解&＃xff0c;如果只是会用&＃xff0c;在很多场景下&＃xff0c;能得到正确结果&＃xff0c;但是碰到这种极端的场景&＃xff0c;就会进坑。

因此在设计开发过程中&＃xff0c;有些环节&＃xff0c;还是要知道原理&＃xff0c;虽然说殊途同归&＃xff0c;但是可能某些路径会消耗更高的成本&＃xff0c;或者产生更多的问题&＃xff0c;这就要持续积累&＃xff0c;持续避坑了。

近期更新的文章&＃xff1a;

《学习工行MySQL研发管控和治理实践的过程》

《MyBatis Generator的使用和坑》

《缓存一致性解决方案介绍》

《从球衣了解“红魔”曼联历史》

《MySQL中SQL执行慢的一种可能的原因场景》

文章分类和索引&＃xff1a;

《公众号900篇文章分类和索引》